--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_clicotehis AS (SELECT * FROM &erp.cli_clicotehis WHERE 1=2);





DELETE FROM se_cli_clicotehis;

 INSERT INTO se_cli_clicotehis
 (SELECT c.id client_cod,
         '1' coteregr_cod,
         '1' cote_typ,
         (SELECT To_Date('31/12/1967','dd/mm/yyyy')+HDCOTEXT FROM UV_CCLIENTS_HDCOTEXT a WHERE a.id=c.id AND a.rangmulti3=c.rangmulti3 ) histo_dat,
         0 enreg_id,
         c.hcotext cote_cod
    FROM UV_CCLIENTS_HCOTEXT c, UV_CCOTESCRL cc,UV_CCLIENTS_COTEXT ccc
    WHERE c.hcotext=cc.id
      AND c.id=ccc.id(+) and c.hcotext=ccc.cotext(+)) ;

 UPDATE se_cli_clicotehis SET cote_cod=REPLACE(cote_cod,'.',',');
 UPDATE se_cli_clicotehis SET cote_cod=REPLACE(cote_cod,'+','P');
 UPDATE se_cli_clicotehis SET cote_cod=REPLACE(cote_cod,'-','M');


 UPDATE se_cli_clicotehis tf SET enreg_id=(select enreg_id FROM &erp.cli_clicotehis f WHERE f.client_cod=tf.client_cod and f.coteregr_cod=tf.coteregr_cod and f.cote_typ=tf.cote_typ and f.histo_dat=tf.histo_dat) ;
 UPDATE se_cli_clicotehis tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('client_cod@coteregr_cod@cote_typ@histo_dat','CLI_CLICOTEHIS','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,client_cod, coteregr_cod, cote_typ, histo_dat, enreg_id, cote_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_cli_clicotehis g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.cli_clicotehis g
  )
GROUP BY client_cod, coteregr_cod, cote_typ, histo_dat, enreg_id, cote_cod
HAVING Count(1)!=2) r
ORDER BY client_cod,coteregr_cod  